﻿using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace TemplatesPrint.DBHelper
{
    public class SQLiteHelper
    {
        public static SqlSugarClient GetInstance()
        {
            SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
            {
                DbType = DbType.Sqlite,
                ConnectionString = @"DataSource=" + Application.StartupPath + @"\DataBase\F.db",
                InitKeyType = InitKeyType.Attribute,
                IsAutoCloseConnection = true,
                LanguageType = LanguageType.Default
            });
            //数据库不存在时进行创建
            //db.DbMaintenance.CreateDatabase();

            //每次Sql执行前事件
            db.Aop.OnLogExecuting = (sql, pars) =>
            {
                var queryString = new KeyValuePair<string, SugarParameter[]>(sql, pars);
                //ToSqlExplain 在标题6中有完整代码
                Console.WriteLine(ToSqlExplain.GetSql(queryString));//输出sql
            };

            //SQL执行完
            db.Aop.OnLogExecuted = (sql, pars) =>
            {
                //执行时间超过1秒
                if (db.Ado.SqlExecutionTime.TotalSeconds > 1)
                {
                    //代码CS文件名
                    var fileName = db.Ado.SqlStackTrace.FirstFileName;
                    //代码行数
                    var fileLine = db.Ado.SqlStackTrace.FirstLine;
                    //方法名
                    var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;
                    //sql
                    var exesql = sql;
                    //参数
                    var sqlpars = pars;
                    //db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 获取上层方法的信息
                }
            };

            //SQL报错
            db.Aop.OnError = (exp) =>
            {
                Console.WriteLine(exp.Sql);
                //exp.sql 这样可以拿到错误SQL            
            };
            return db;
        }

        class ToSqlExplain
        {
            public static string GetSql(KeyValuePair<string, SugarParameter[]> queryString)
            {
                var sql = queryString.Key;//sql语句
                var par = queryString.Value;//参数

                //字符串替换MethodConst1x会替换掉MethodConst1所有要从后往前替换,不能用foreach,后续可以优化
                for (int i = par.Length - 1; i >= 0; i--)
                {
                    if (par[i].ParameterName.StartsWith("@") && par[i].ParameterName.Contains("UnionAll"))
                    {
                        sql = sql.Replace(par[i].ParameterName, par[i].Value.ToString());
                    }
                }

                for (int i = par.Length - 1; i >= 0; i--)
                {
                    if (par[i].ParameterName.StartsWith("@Method"))
                    {
                        sql = sql.Replace(par[i].ParameterName, "'" + par[i].Value.ToString() + "'");
                    }
                }
                for (int i = par.Length - 1; i >= 0; i--)
                {
                    if (par[i].ParameterName.StartsWith("@Const"))
                    {
                        sql = sql.Replace(par[i].ParameterName, par[i].Value.ToString());
                    }
                }
                for (int i = par.Length - 1; i >= 0; i--)
                {
                    if (par[i].ParameterName.StartsWith("@"))
                    {
                        //值拼接单引号 拿出来的sql不会报错
                        sql = sql.Replace(par[i].ParameterName, "'" + Convert.ToString(par[i].Value) + "'");
                    }
                }
                return sql;
            }
        }

    }

}
